﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace CmccTool.KPI.Services
{
    internal class KpiCheck
    {
        #region 生成校验sql
        /// <summary>
        /// 根据配置文件，输出统计数据库内容的，生成工具
        /// </summary>
        /// <param name="configs">配置文件</param>
        /// <param name="dateField">默认日期列</param>
        /// <returns></returns>
        public static string GenerateSql(
            List<AppConfig.SqlQueryConfig> configs,
            string dateField = "date") // 默认日期字段
        {

            Dictionary<string, string> dicTabName = [];
            var sql = new StringBuilder(string.Empty);
            var sql1 = new StringBuilder($"WITH all_dates AS ({Environment.NewLine}");
            var sql2 = new StringBuilder($"SELECT t0.{dateField}, ");
            var sql3 = new StringBuilder($@"FROM (SELECT DISTINCT {dateField} FROM all_dates where {dateField} >= '{DateTime.Today.AddYears(-1):yyyy-MM-dd}' ) AS t0");

            // 动态生成每个配置对应的子查询
            for (int i = 0; i < configs.Count; i++)
            {
                var config = configs[i];
                if (dicTabName.TryAdd(config.TableName, string.Empty))
                {
                    if (i != 0) sql1.AppendLine("    UNION");
                    sql1.AppendLine($"    SELECT date FROM {config.TableName}");
                }

                string alias = $"t{i + 1}";

                // 构建子查询
                sql2.Append($"{Environment.NewLine}COALESCE({alias}.{config.OutputAlias}, 0) AS {config.OutputAlias}, ");

                // 生成 JOIN 部分
                sql3.Append($@"{Environment.NewLine}LEFT JOIN (");
                sql3.Append($@"SELECT {config.DateField},  COUNT(*) AS {config.OutputAlias} FROM {config.TableName} ");
                sql3.Append($@"{(!string.IsNullOrEmpty(config.ConditionField) ? $@" where {config.ConditionField} = '{config.ConditionValue}'" : string.Empty)}");
                sql3.Append($@"GROUP BY {config.DateField})");
                sql3.Append($@"{alias} ON t0.{dateField} = {alias}.{config.DateField}");


            }

            sql1.Append(')');
            // 移除末尾逗号并添加主日期表
            sql2.Length -= 2;

            sql.AppendLine(sql1.ToString());
            sql.AppendLine(sql2.ToString());
            sql.AppendLine(sql3.ToString());
            sql.Append($@"order by t0.{dateField} desc");

            return sql.ToString();
        }

        #endregion
    }
}
